<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE dita PUBLIC "-//OASIS//DTD DITA Composite//EN" "ditabase.dtd">
<dita>
  <topic id="topic_gpsv">
    <title>gp_legacy_string_agg</title>
    <body>
      <p>The <codeph>gp_legacy_string_agg</codeph> module re-introduces the single-argument
        <codeph>string_agg()</codeph> function that was present in Greenplum Database 5.</p>
      <p>The <codeph>gp_legacy_string_agg</codeph> module is a Greenplum Database extension.</p>
      <note>Use this module to aid migration from Greenplum Database 5 to the native,
        two-argument <codeph>string_agg()</codeph> function included in Greenplum 6.</note>
    </body>
    <topic id="topic_reg">
      <title>Installing and Registering the Module</title>
      <body>
        <p>The <codeph>gp_legacy_string_agg</codeph> module is installed when you install
          Greenplum Database. Before you can use the function defined in the module, you
          must register the <codeph>gp_legacy_string_agg</codeph> extension in each database
          where you want to use the function.
          <ph otherprops="pivotal">Refer to <xref href="../../install_guide/install_modules.html"
            format="html" scope="external">Installing Additional Supplied Modules</xref>
          for more information about registering the module.</ph></p>
      </body>
    </topic>
    <topic id="topic_use">
      <title>Using the Module</title>
      <body>
        <p>The single-argument <codeph>string_agg()</codeph> function has the following
          signature:</p>
        <codeblock>string_agg( text )</codeblock>
        <p>You can use the function to concatenate non-null input values into a string. For
          example:</p>
        <codeblock>SELECT string_agg(a) FROM (VALUES('aaaa'),('bbbb'),('cccc'),(NULL)) g(a);
WARNING:  Deprecated call to string_agg(text), use string_agg(text, text) instead
  string_agg  
--------------
 aaaabbbbcccc
(1 row)</codeblock>
        <p>The function concatenates each string value until it encounters a null value,
          and then returns the string. The function returns a null value when no rows are
          selected in the query.</p>
        <p><codeph>string_agg()</codeph> produces results that depend on the ordering of the
          input rows. The ordering is unspecified by default; you can control the ordering by
          specifying an <codeph>ORDER BY</codeph> clause within the aggregate. For example:</p>
        <codeblock>CREATE TABLE table1(a int, b text);
INSERT INTO table1 VALUES(4, 'aaaa'),(2, 'bbbb'),(1, 'cccc'), (3, NULL);
SELECT string_agg(b ORDER BY a) FROM table1;
WARNING:  Deprecated call to string_agg(text), use string_agg(text, text) instead
  string_agg  
--------------
 ccccbbbb
(1 row)</codeblock>
      </body>
    </topic>
    <topic id="topic_migrate">
      <title>Migrating to the Two-Argument string_agg() Function</title>
      <body>
        <p>Greenplum Database 6 includes a native, two-argument, text input
          <codeph>string_agg()</codeph> function:</p>
        <codeblock>string_agg( text, text )</codeblock>
        <p>The following function invocation is equivalent to the single-argument
          <codeph>string_agg()</codeph> function that is provided in this module:</p>
        <codeblock>string_agg( text, '' )</codeblock>
        <p>You can use this conversion when you are ready to migrate from this contrib module.</p>
      </body>
    </topic>
  </topic>
</dita>
